package com.cap.bts.crmdp.dealer.remind.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import javax.inject.Inject;

import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.engine.SessionFactoryImplementor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.remind.dao.IVehicleDao;
import com.cap.bts.crmdp.dealer.remind.domain.Vehicle;
import com.cap.bts.crmdp.dealer.remind.dto.VehicleDto;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;

@Repository("vehicleDao")
public class VehicleDaoImpl extends BaseDaoImpl<Vehicle, Long> implements IVehicleDao {
	
	@Inject
	public VehicleDaoImpl(@Value("com.cap.bts.crmdp.dealer.remind.domain.Vehicle") Class<Vehicle> clazz) {
		super(clazz);
	}
	
	private static final Logger LOGGER = Logger.getLogger(VehicleDaoImpl.class);

	@Override
	public Vehicle getVehicleByVehicleID(String vehicleID) throws AppException {
		String sql = "select * from tm_raw_vehicle where vehicle_id='" + vehicleID + "'";
		List<Vehicle> list = this.getListByNativeQuery(sql);
		if(list != null && !list.isEmpty()){
			Vehicle vehicle = list.get(0);
			return vehicle;
		}
		return null;
	}

	@Override
	public void deleteAllExistVehicles(List<String> vehicleIdList) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		try {
			LOGGER.info("start delete vehicles");
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			
			pstmt = connection.prepareStatement("delete from tm_raw_vehicle where vehicle_id = ?");
			for (int i = 0; i < vehicleIdList.size(); i++) {
				pstmt.setString(1, vehicleIdList.get(i));
				pstmt.addBatch();
			}
			
			LOGGER.info("start delete commit");
			pstmt.executeBatch();     
			connection.commit();     
			LOGGER.info("end delete commit");
		} catch (Exception e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end delete vehicles");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
		
	}

	@Override
	public void insertAllVehicles(List<VehicleDto> list) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		int i = 0;
		try {
			LOGGER.info("start insert vehicles");
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			pstmt = connection.prepareStatement("INSERT INTO tm_raw_vehicle  ( " +
			        "    service_center_id , " +
			        "    vin , " +
			        "    customer_id , " +
			        "    vehicle_brand_name , " +
			        "    vehicle_model , " +
			        "    vehicle_variant , " +
			        "    vehicle_id , " +
			        "    year_time , " +
			        "    size_of_engine , " +
			        "    ly_id , " +
			        "    license_plate , " +
			        "    creation_date , " +
			        "    last_modification_date , " +
			        "    register_date , " +
			        "    crm_member," +
			        "    create_by," +
			        "    create_date," +
			        "    last_update_by," +
			        "    last_update_date" +
			        ") " +
			        "VALUES " +
			        "   ( " +
			        "   ? ,  ? , ? ,  ? ,  ? , " + 
			        "  	? ,  ? , ? ,  ? ,  ? , " +
			        "  	? ,  ? , ? ,  ? ,  ? ,"
			        + " ?,?,?,?  " +
			        "   ) ");
			for(VehicleDto vehicleDto:list){
				if(vehicleDto == null){
					continue;
				}
				i++;
				if(vehicleDto.getServiceCenterID() != null){
					pstmt.setLong(1, vehicleDto.getServiceCenterID());
				}else{
					pstmt.setNull(1,java.sql.Types.BIGINT);
				}
//				pstmt.setLong(1, vehicleDto.getServiceCenterID());
				pstmt.setString(2, vehicleDto.getVin());
				pstmt.setString(3, vehicleDto.getCustomerID());
				pstmt.setString(4, vehicleDto.getVehicleBrandName());
				pstmt.setString(5, vehicleDto.getVehicleModel());
				pstmt.setString(6, vehicleDto.getVehicleVariant());
				pstmt.setString(7, vehicleDto.getVehicleID());
				Long temp =  vehicleDto.getYear() != null ?  vehicleDto.getYear() : null;
				if(temp == null){
					pstmt.setNull(8, java.sql.Types.BIGINT);
				}else{
					pstmt.setLong(8, temp);
				}
				pstmt.setString(9, vehicleDto.getSizeOfEngine());
				pstmt.setString(10, vehicleDto.getLyID());
				pstmt.setString(11, vehicleDto.getLicensePlate());
				if(vehicleDto.getCreationDate() != null){
					pstmt.setDate(12, new Date(vehicleDto.getCreationDate().getTime()));
				}else{
					pstmt.setNull(12,java.sql.Types.DATE);
				}
				if(vehicleDto.getLastModificationDate() != null){
					pstmt.setDate(13, new Date(vehicleDto.getLastModificationDate().getTime()));
				}else{
					pstmt.setNull(13,java.sql.Types.DATE);
				}
				if(vehicleDto.getRegisterDate() != null){
					pstmt.setDate(14, new Date(vehicleDto.getRegisterDate().getTime()));
				}else{
					pstmt.setNull(14,java.sql.Types.DATE);
				}
				pstmt.setString(15, vehicleDto.getCrmMember());
				pstmt.setLong(16, 0L);
				pstmt.setDate(17, new Date(new java.util.Date().getTime()));
				pstmt.setLong(18, 0L);
				pstmt.setDate(19, new Date(new java.util.Date().getTime()));
				pstmt.addBatch();
//				LOGGER.info(i);
				if(i%1000 == 0){
					LOGGER.info("start commit");
					pstmt.executeBatch();     
					connection.commit();     
					LOGGER.info("end commit");
					pstmt.clearBatch(); 
				}
			}
			if(i%1000 != 0){
				LOGGER.info("start commit");
				pstmt.executeBatch();     
				connection.commit();  
				LOGGER.info("end commit");
			}
		} catch (Exception e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end insert customer");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
		
	}

}
